<!DOCTYPE html>
<html lang="zh-CN">
    <head>
        <meta charset="utf-8"/>
        <title>Reporter Demo</title>
        <script src="js/jquery-1.8.3.min.js" type="text/javascript"></script>
        <script type="text/javascript">
            (function(scope){
                var col_const = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J'
                           , 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T'
                           , 'U', 'V', 'W', 'X', 'Y', 'Z']; //支持的列数，可以在这里按照Excel的标签方式扩展
            
                // 解析表格（通过id）
                var parse = function(self) {
                    var id = self.id;
                    var tableWithId = null;

                    if (/^#.*/.test(id) || /^\..*/.test(id)) {
                        tableWithId = $(id);
                    } else {
                        tableWithId = $("#" + id);
                    }
                    
                    self.table = tableWithId;
                    
                    if (tableWithId.size() == 0)
                        throw "element by id: " + id + " not found in dom!";

                    var table_trs = tableWithId.find("tr");
                    for (var i = 0, j = table_trs.size(); i < j; i++) {
                        var raw_tr = table_trs[i];
                        var table_tr = $(raw_tr);
                        if (table_tr.hasClass("report_head")) continue;
                        var row_cols = table_tr.find("td");
                        for (var m = 0, n = row_cols.size(); m < n; m++) {
                            var raw_col = row_cols[m];
                            var row_col = $(raw_col);
                            row_col.attr("col_label", col_const[m] + "" + i);
                        }
                    }
                };
            
                // 名字空间
                var Scanner = function(id){
                    if (!id || typeof id != "string")
                        throw "id is empty or id is invalid";
                    this.table = null; // 保存id所指向的表格的jq对象
                    this.id = id;
                    this.funcs = {}; // 函数，包含单元格运算函数，如：A1+B1，第三方函数，如：QMYE("货币资金")，以及它们的混合运算
                    this.vars = {}; // 变量，只包含单元格值，如C1，D2，这个值的内容分两种：1，从后台获取，2运算所得（如C1=A1+B1）
                    this.expressionInvoke = {}; // 第三方函数调用所映射的值，这个值内容都是从后台获取
                    this._funcs = {};
                    parse(this);
                };
            
                var isNumber = function(src) {
                    try{
                        parseInt(src);
                    } catch(e) {
                        return false;
                    }
                    return true;
                };
                
                // 设置Scanner的埋设的公式或者内容
                Scanner.prototype.conditions = function(conds) {
                    var self = this;
                    for (var key in conds) {
                        var val = conds[key] || "";
                        var target = this.table.find("td[col_label='" + key + "']");
                        if (target.size() > 0) {
                            //preprocess val A1:A2->'A1:A2' or A1:G3->'A1:G3'
                            val = val.replace(/\b\w?\d?:\w?\d?\b/g, function(p){
                                return "'" + p + "'";
                            });
                            
                            if (/^=(.*)$/.test(val)) {
                                var F = new Function("var result = 0; with(this.funcs) { with(this.vars) { result = " + RegExp.$1 + ";} } return result;"); //先找变量，再找函数
                                self.funcs[key] = (function(F){ return function() {
                                    return F.call(self);
                                }; })(F);
                            } else {
                                if (isNumber(val))
                                    self.vars[key] = parseInt(val);
                                else
                                    self.vars[key] = val;
                            }
                            target.attr("title", val);
                        }
                    }
                    return self;
                };
                
                // 设置表达式，就是第三方公式，比如QMYE，期末余额
                Scanner.prototype.expressions = function(exprs) {
                    var self = this;
                    for (var k in exprs) {
                        this.expressionInvoke[k] = exprs[k];
                        this.funcs[k] = (function(k){
                            return function(v) {
                                return self.expressionInvoke[k][v] || 0;
                            };
                        })(k);
                    }
                    return self;
                };
                
                // 设置单元格的值，这里数据来源后台
                Scanner.prototype.values = function(vals) {
                    for (var k in vals) {
                        this.vars[k] = vals[k];
                    }
                    return this;
                };
                
                // 计算依赖，有时候会出现前面的单元格引入后面单元格的情况，会导致计算失败，这里会重试计算任务，deep表示重试次数
                var processDependsCalc = function(self, dependsCalc, deep) {
                    if (deep <= 0) return;
                    var postCalc = [];
                    if (dependsCalc.length > 0) {
                        for (var i = 0, j = dependsCalc.length; i < j; i++) {
                            var tmp = dependsCalc[i];
                            var col_label = tmp.col_label;
                            var td = tmp.td;
                            var func = self.funcs[col_label];
                            if (func) {
                                var value = func();
                                if (isNaN(value)) {
                                    var tmp = {"col_label": col_label, "td": td};
                                    postCalc.push(tmp);
                                    continue;
                                }
                                td.html(value + "");
                                self.vars[col_label] = value;
                            } else {
                                var value = self.values[col_label];
                                if (value)
                                    td.html(value);
                            }
                        }
                    }
                    if (postCalc.length > 0) {
                        processDependsCalc(self, postCalc, deep - 1);
                    }
                };
                
                // 计算开始
                Scanner.prototype.process = function(){
                    for (var f in this._funcs) {
                        this.funcs[f] = this._funcs[f];
                    }
                    var tds = this.table.find("td[col_label]");
                    var dependsCalc = [];
                    for (var i = 0, j = tds.size(); i < j; i++) {
                        var td = $(tds[i]);
                        var col_label = td.attr("col_label");
                        if (col_label) {
                            var func = this.funcs[col_label];
                            if (func) {
                                var value = null;
                                try{
                                    value = func();
                                } catch(e) {
                                    console.log(e);
                                }
                                if (isNaN(value)) {
                                    var tmp = {"col_label": col_label, "td": td};
                                    dependsCalc.push(tmp);
                                    continue;
                                }
                                td.html(value + "");
                                this.vars[col_label] = value;
                            } else {
                                var value = this.vars[col_label];
                                if (value)
                                    td.html(value);
                            }
                        }
                    }
                    processDependsCalc(this, dependsCalc, 3);
                };
                
                Scanner.prototype.innerExpr = function(exprs) {
                    var self = this;
                    for (var k in exprs) {
                        self._funcs[k] = (function(self, key){
                            return function(v) {
                                return exprs[key](self, v);
                            };
                        })(self, k);
                    }
                    return self;
                };
                
                scope.Scanner = Scanner;
            })(window);
            
            $(function(){
                var conds = {
                    "C1": "=A1*B1+QMYE('应收账款')+CAO('xxx')",
                    "C2": "=A2*B2*2",
                    "C3": "=A3*B3",
                    "B4": "=A4*C4",
                    "A4": "=100*2",
                    "C4": "=(QMYE('银行存款')+QMYE('库存现金'))/100-9.9+C5",
                    "C5": "=C1+C2+C3",
                    "C6": "=SUM(C1:C5)",
                    "B6": "=MAX(B4:C5)"
                };
                var vals = {
                    "A1": 2,
                    "B1": 5,
                    "A2": 10,
                    "B2": 2,
                    "A3": 6,
                    "B3": 8,
                    "A6": "合计："
                };
                var exprs = {
                    QMYE: {"银行存款": 100, "库存现金": 200},
                    CAO: {'xxx': 100}
                };
                var sysExpr = {
                    "SUM": function(self, params) { //A1:An
                        var ps = params.split(":");
                        if (ps.length != 2) return 0;
                        var label, startIndex, endIndex;
                        if (/(\w+)(\d+)/.test(ps[0])) {
                            label = RegExp.$1;
                            startIndex = parseInt(RegExp.$2);
                        }
                        if (/(\w+)(\d+)/.test(ps[1])) {
                            if (label != RegExp.$1) return 0;
                            endIndex = parseInt(RegExp.$2);
                        }
                        var result = 0;
                        for (var i = startIndex; i <= endIndex; i++) {
                            var val = self.vars[label + i + ""];
                            if (!val) return NaN;
                            result = result + (val || 0);
                        }
                        return result;
                    },
                    
                    "MAX": function(self, params) {
                        var col_const = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J'
                           , 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T'
                           , 'U', 'V', 'W', 'X', 'Y', 'Z']; 
                        var ps = params.split(":");
                        if (ps.length != 2) return 0;
                        var labelA, labelB, startIndex, endIndex;
                        if (/(\w+)(\d+)/.test(ps[0])) {
                            labelA = RegExp.$1;
                            startIndex = parseInt(RegExp.$2);
                        }
                        if (/(\w+)(\d+)/.test(ps[1])) {
                            labelB = RegExp.$1;
                            endIndex = parseInt(RegExp.$2);
                        }
                        var result = 0, colStart, colEnd;
                        for (var i = 0, j = col_const.length; i < j; i++) {
                            if (col_const[i] == labelA) {
                                colStart = i;
                            }
                            if (col_const[i] == labelB) {
                                colEnd = i;
                            }
                        }
                        for (var j = colStart; j <= colEnd; j++) {
                            for (var i = startIndex; i <= endIndex; i++) {
                                debugger;
                                var val = self.vars[col_const[j] + i + ""];
                                console.log(j + i + "=" + val);
                                if (val && val > result) result = val;
                            }    
                        }
                        return result;
                    }
                };
                new Scanner(".report").innerExpr(sysExpr).conditions(conds).expressions(exprs).values(vals).process();
            });
        </script>
        <style type="text/css">
            table {border: #ccc 1px solid;}
            table tr td {border: #ccc 1px solid;}
        </style>
    </head>
    <body>
        <table class="report">
            <tr class="report_head"><th>金额</th><th>数量</th><th>总金额</th></tr>
            <tr><td style="text-align: right"></td><td></td><td></td></tr>
            <tr><td style="text-align: right"></td><td></td><td></td></tr>
            <tr><td style="text-align: right"></td><td></td><td></td></tr>
            <tr><td style="text-align: right"></td><td></td><td></td></tr>
            <tr><td style="text-align: right"></td><td></td><td></td></tr>
            <tr><td style="text-align: right"></td><td></td><td></td></tr>
        </table>
    </body>
</html>